Setting Dimension Order for a TM1 Cube

One of the key areas to think about when designing a TM1 model is dimension order in cubes. This can be important as it has impact on how your model performs, how much memory it consumes, how usable it is and how it interacts with BI.

Two Dimension Orders in TM1

It is important to note initially that there are two dimension orders used by TM1. One that impacts the performance of the cube and the other how the cube is presented.

Presentation Order of Dimension

When we first design a cube we should always firstly consider how usable the design will be and how consistent the cube is with other cubes in the model. For example, if we have Year, Month and Version in every (or most) cubes, then having them as the first three dimensions will make it easier for users to navigate. Then we might want to have other less dense dimensions, followed by the measures dimension as the final dimension.

We set this order when we first create a cube. This initial dimension order definition sets both the physical and presentation orders, however although the physical order can be modified later, the presentation order will remain fixed (thus when you have used dimensions or elements in rules or processes, they will continue to work as the definition will be in the presentation order, which can’t change).

Physical Reorder of Dimensions

We can change the physical order of dimensions subsequently by right clicking on a cube and selecting Reorder Dimensions. This allows us to optimise the cube for memory consumption and performance.

Therefore we should always set the order of dimensions from a usability and consistency perspective initially, noting that we can tune them for performance later.

When we then start optimising for performance there are two golden rules for TM1 dimension order. They are:

• Firstly order dimensions from smallest to largest,
• Then order dimensions from sparsest to densest

Explanation of Smallest to Largest, Sparsest to Densest

What does this mean though?

Well, the smallest to largest is pretty obvious. If a dimension has a fixed number of elements, say there are 4 versions, or there is a variable length dimension, say product, where we start with 10,000 products and expect it to grow by 2,000 new products every year, then clearly the product dimension is larger than then version dimension.

For sparsest to densest, that is a little tricker. If we have a month dimension with 12 elements in it and expect sales every month, then that would be considered a very dense dimension. If we then think of a customer dimension also with 10,000 customers in it, where a customer buys once every year on average, then that would be significantly sparser than the product dimension where we sell most products most of the time.

So to work this out literally list the dimensions in a spreadsheet, then add the number of elements in the next column and then some sort of indicator in the next column about how dense you understand the data to be populated across the elements in the dimension. This then will guide you about how to define the dimension order.

This might result in the following (we have used a scale of 1-5 to indicate our understanding of how dense the dimension is, where 1 is sparse and 5 is dense):

So, if we used the dimensions listed above and applied the “firstly order dimensions from smallest to largest, then order dimensions from sparsest to densest” rule, we would use something like the following order: Version, Year, Month, Measures, Customer, Rep, Product.

The Most Important Dimension

Basically, the largest dimension with the highest density should be the last one in the cube and you get massively better performance from getting this very last dimension right. There is a significantly diminishing performance improvement for each next dimension thereafter.

Accurate Calculation of Dimension Order

Finally, there is a more accurate method of calculating the dimension order. This involves the following:

1. Analyse out the number of N level elements in a dimension:
   1. Create a zero level Subset in each dimension,
   2. List the dimensions in a cube in a spreadsheet in the first column,
   3. Use the SubSiz(dimension, subset) function in Excel to get the number of elements in each dimension. Do this in the second column.
2. For each dimension other than the measures dimension, work out the number of populated cells:
   1. Create a View for each of the zero level Subsets created above, where:
      1. the subset is placed into rows (e.g. “Product: Level 0”,
      2. a single measure that is used right through the cube is a column (e.g. “Sales”, and
      3. all other dimensions are set to be the very top of the dimension structure (e.g. “Total Customers”)
   2. Enable zero suppression on the view
   3. Dump it to Excel and count the number of records.
   4. Enter this value beside the SubSiz formula above in the third column
3. To get the density, calculate the ratio of the number of N level elements (from A above) to the number that are populated (from B).
4. Finally, rank the dimensions by density and then size (from densest to sparsest and biggest to smallest). This will tell you what last dimension in the cube should be, and then the second last etc.

Interaction with BI

Here’s where it starts to get a bit messy. Cognos Analytics is now a great solution for presenting TM1 cubes over the web for either structured reporting or ad hoc analysis. However, Cognos BI demands that the time dimension be the first and the measures dimension be last in your cube.

There are other considerations to take into account when using TM1 with BI which are covered in other posts on ExploringTM1.com, especially those to do with performance. The need to have the time and measures dimensions in this order is one of the primary reasons that you might like to consider having a dedicated reporting cube that is then presented to BI and draws data from your active TM1 cubes.